package com.bodacn.packapp;

import android.util.Log;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.net.HttpURLConnection;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;


public class DBUtil
{

    private static final int REQUEST_TIMEOUT = 60 * 1000;//设置超时60秒

    public static Connection getSQLConnection()
    {
        Connection conn = null;
        try
        {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
        } catch (ClassNotFoundException e)
        {
            System.out.println("加载数据库引擎失败");
            e.printStackTrace();
        }
        try{
            String connectDB = "jdbc:jtds:sqlserver://"+MainApplication.ServerIP+";DatabaseName="+MainApplication.DBName;
            conn = DriverManager.getConnection(connectDB, MainApplication.UserName, MainApplication.Password);
//            String connectDB = "jdbc:jtds:sqlserver://121.229.24.37;DatabaseName=BDDMSNew";
//            conn = DriverManager.getConnection(connectDB, "boda", "P@ssw0rd");
        }
        catch (SQLException e)
        {
            System.out.println("创建数据库连接失败!");
            e.printStackTrace();
        }
        return conn;
    }

    //两种方式调用
    public static JSONArray QuerySQL2JSON(String paramSQLCommand)
    {
        if (MainApplication.apiUrl!=null&&MainApplication.apiUrl.length()>0) return WebQuerySQL2JSON( paramSQLCommand);
        else return DirQuerySQL2JSON(paramSQLCommand);
    }
    //直接调用
    public static JSONArray DirQuerySQL2JSON(String paramSQLCommand)
    {
        System.out.println("DirQuerySQL2JSON");
        // json数组
        JSONArray array = new JSONArray();
        try
        {
            Connection conn = getSQLConnection();
            if (conn!=null) {
                System.out.println(paramSQLCommand+"-------->准备执行");
                String sqlcmd = paramSQLCommand;
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(sqlcmd);
                //取字段相关信息;
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                //遍列数据;
                while (rs.next()) {
                    JSONObject jsonObj = new JSONObject();
                    // 遍历每一列
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = metaData.getColumnLabel(i);
                        Object value = rs.getObject(columnName);
                        if (value==null) value="";
                        jsonObj.put(columnName, value);
                    }
                    array.put(jsonObj);
                }
                rs.close();
                stmt.close();
                conn.close();
            }
        } catch (SQLException e)
        {
            e.printStackTrace();
            System.out.println(paramSQLCommand+"-------->异常");
            JSONObject jsonObj = new JSONObject();
            try {
                jsonObj.put("SQLExcept","SQL查询异常-A");
                jsonObj.put("SQLMessage",e.getMessage());
                array.put(jsonObj);
            } catch (JSONException e1) {
                e1.printStackTrace();
            }
        }catch (JSONException e){
            e.printStackTrace();
            JSONObject jsonObj = new JSONObject();
            try {
                jsonObj.put("SQLExcept","SQL查询异常-B");
                jsonObj.put("SQLMessage",e.getMessage());
                array.put(jsonObj);
            } catch (JSONException e1) {
                e1.printStackTrace();
            }
        }
        return array;
    }
    //WEB调用
    public static JSONArray WebQuerySQL2JSON(final String paramSQLCommand)
    {
        System.out.println("WebQuerySQL2JSON");
        // json数组
        JSONArray array = new JSONArray();
        String jsonParam="";
        BufferedReader reader = null;
        try {
            String path =MainApplication.apiUrl +"/api/QuerySQL2JSON";
            System.out.println(path);
            JSONObject jsonObject=new JSONObject();
            jsonObject.put("sqlComm",paramSQLCommand);
            jsonParam=jsonObject.toString();
            Log.e("QuerySQL2JSON","jsonParam:"+jsonParam);
            URL url = new URL(path);// 创建连接
            HttpURLConnection connection = (HttpURLConnection) url.openConnection();
            connection.setDoOutput(true);
            connection.setConnectTimeout(REQUEST_TIMEOUT);
            connection.setDoInput(true);
            connection.setUseCaches(false);
            connection.setInstanceFollowRedirects(true);
            connection.setRequestMethod("POST"); // 设置请求方式
            connection.setRequestProperty("Content-Type", "application/json"); // 设置发送数据的格式
            //设置发送数据长度（用于发送大量数据使用）
            //connection.setRequestProperty("Content-Length", String.valueOf(jsonParam.length()));
            //一定要用BufferedReader 来接收响应， 使用字节来接收响应的方法是接收不到内容的
            OutputStreamWriter out = new OutputStreamWriter(connection.getOutputStream(), "UTF-8"); // utf-8编码
            out.append(jsonParam);
            out.flush();
            out.close();
            Log.e("QuerySQL2JSON","getResponseCode:"+String.valueOf(connection.getResponseCode()));
            if (connection.getResponseCode() == HttpURLConnection.HTTP_OK) {
                // 读取响应
                reader = new BufferedReader(new InputStreamReader(connection.getInputStream(), "UTF-8"));
                String line;
                String res = "";
                while ((line = reader.readLine()) != null) {
                    res += line;
                }
                Log.d("QuerySQL2JSON",res);
                reader.close();
                array=new JSONArray(res);
                return array;
            } else {
                JSONObject jsonObj = new JSONObject();
                try {
                    jsonObj.put("SQLExcept","请求异常");
                    jsonObj.put("SQLMessage","请求异常,请检查网络");
                    array.put(jsonObj);
                } catch (JSONException e1) {
                    e1.printStackTrace();
                }
                return array;
            }
        } catch (IOException | JSONException e) {
            e.printStackTrace();
            JSONObject jsonObj = new JSONObject();
            try {
                jsonObj.put("SQLExcept","请求异常");
                jsonObj.put("SQLMessage",e.getMessage());
                array.put(jsonObj);
            } catch (JSONException e1) {
                e1.printStackTrace();
            }
            return array;
        }
        finally {
            if(reader!=null){
                try{
                    System.out.println("QuerySQL2JSON:Close-1");
                    reader.close();
                    System.out.println("QuerySQL2JSON:Close-2");
                }catch (IOException e){
                    throw new RuntimeException("QuerySQL2JSON:关闭失败");
                }
            }
        }


    }

    public static void main(String[] args)
    {

    }
}
